package jdbc;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;

public class DBHelper {

    //获取1行1列
    public static Object get(String sql , Object[] obj) throws Exception {
        Object ob = null;
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            ps = conn.prepareStatement(sql);
            if(obj!=null && obj.length>0){
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i+1, obj[i]);
                }
            }
            rs= ps.executeQuery() ;
            if(rs.next()) {
                ob = rs.getObject(1) ;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            // 关闭资源,这里可以手动判断是否为空关闭.注:由最后使用的对象依次向前关闭,如上关闭顺序 rs-->ps
            JDBCUtils.release(null, ps, conn);
        }
        return ob;
    }

    public static int update(String sql,Object[] obj) throws Exception {
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        int rows = 0 ;
        try {
            ps = conn.prepareStatement(sql);
            if(obj!=null && obj.length>0){
                for (int i = 1; i <= obj.length; i++) {
                    ps.setObject(i, obj[i-1]);
                }
            }
            rows= ps.executeUpdate() ;
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            // 关闭资源,这里可以手动判断是否为空关闭.注:由最后使用的对象依次向前关闭,如上关闭顺序 rs-->ps
            JDBCUtils.release(null, ps, conn);
        }
        return rows;
    }

    public static Command build(String sql, Object... params) {
        return Command.build().sql(sql).params(params);
    }

    public static boolean transaction(Command... commands) throws Exception {
        if (commands == null || commands.length == 0) {
            return false;
        }
        Connection conn = JDBCUtils.getConnection();
        List<PreparedStatement> statements = new ArrayList<>();
        try {
            conn.setAutoCommit(false);
            for (Command command : commands) {
                String sql = command.getSql();
                List<Object> params = command.getParams();
                PreparedStatement statement = conn.prepareStatement(sql);
                if (params != null) {
                    for (int i = 0; i < params.size(); i++) {
                        statement.setObject(i + 1, params.get(i));
                    }
                }
                statements.add(statement);
                if (statement.executeUpdate() <= 0) {
                    throw new RuntimeException("Rollback.");
                }
            }
            conn.commit();
            return true;
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (Exception ex) {
                e.printStackTrace();
            }
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
            for (PreparedStatement statement : statements) {
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

    public static boolean transaction(String... sqls) throws Exception {
        if (sqls == null || sqls.length == 0) {
            return false;
        }
        Connection conn = JDBCUtils.getConnection();
        List<PreparedStatement> statements = new ArrayList<>();
        try {
            conn.setAutoCommit(false);
            for (String sql : sqls) {
                PreparedStatement statement = conn.prepareStatement(sql);
                statements.add(statement);
                if (statement.executeUpdate() <= 0) {
                    throw new RuntimeException("Rollback.");
                }
            }
            conn.commit();
            return true;
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (Exception ex) {
                e.printStackTrace();
            }
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
            for (PreparedStatement statement : statements) {
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

    public static List<Map<String,Object>> query(String sql , Object[]st) throws Exception {
        List<Map<String , Object>> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            if(st!=null && st.length>0){
                for (int i = 0; i < st.length; i++) {
                    ps.setObject(i+1, st[i]);
                }
            }
            rs = ps.executeQuery() ;
            // 获取结果集元数据对象, 元(Meta)数据(data): 描述数据的数据
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                // 创建 map 集合
                /**
                 * 1. HashMap   	无序不重复
                 * 2 LinkedHashMap, 有序不重复
                 * 3. TreeMap 		排序不重复
                 */
                Map<String , Object> map = new LinkedHashMap<>() ;
                for(int i=0;i<rsmd.getColumnCount();i++){
                    String name = rsmd.getColumnLabel(i+1);
                    Object values = rs.getObject(name);
                    map.put(name, values);
                }
                list.add(map) ;
            }
        }catch(Exception e) {
            e.printStackTrace();
            //正常写法是依次获取相应的异常抛出交给上层处理
        }finally{
            // 关闭资源,这里可以手动判断是否为空关闭.注:由最后使用的对象依次向前关闭,如上关闭顺序 rs-->ps
            JDBCUtils.release(rs, ps, conn);
        }
        return list ;
    }

    public static <T> T query(Class<T> clazz, String sql, Object... args) throws Exception {

        T entity = null;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取resultSet
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            // 获取resultSetMetaData
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            // 创建一个MAP 键 :SQL查询得到的列的别名，别名和javaBean的属性名对应 值:对应列的值
            Map<String, Object> values = new HashMap<>();
            // 处理结果集，利用resultSetMetaData填充Map
            while (resultSet.next()) {
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    String columnLable = resultSetMetaData
                            .getColumnLabel(i + 1);
                    Object columnValue = resultSet.getObject(i + 1);

                    values.put(columnLable, columnValue);
                }
            }

            // 如果map不为空，利用反射创建对象
            if (!values.isEmpty()) {
                // 反射创建对象
                entity = clazz.newInstance();
                // 遍历Map ,利用反射为属性赋值
                for (Map.Entry<String, Object> entry : values.entrySet()) {
                    String fieldName = entry.getKey();
                    Object value;
                    // 因为java中和mysql中数据类型对应不一致
                    // mysql中int对应JAVA中的long，所以当查询出int型数据的时候需要转换
                    if (entry.getValue() instanceof Long) {
                        value = Integer.parseInt(entry.getValue().toString());
                    } else {
                        value = entry.getValue();
                    }
                    // ReflectionUtils.setFieldValue(entity, fieldName, value);
                    Field field = clazz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    field.set(entity, value);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            // 关闭资源,这里可以手动判断是否为空关闭.注:由最后使用的对象依次向前关闭,如上关闭顺序 rs-->ps
            JDBCUtils.release(resultSet, preparedStatement, connection);
        }

        return entity;
    }

}
